[Oracle Debug] 實現用regexp_substr分解資料成多個欄位


Posted by RedPanda56 on 2022-06-17

* 目標

給定資料如下:將此字串分割成三個欄位

SELECT '1^^2' AS temp_value
FROM dual

* 使用regexp_substr (方法1)

WITH temp AS
(
    SELECT '1^2^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3) AS cc
FROM temp

* 輸出結果

aa bb cc
1 2 3

* 使用regexp_substr (方法2)

當遇到有「空值」的時候,方法1會造成資料和欄位錯置

WITH temp AS
(
    SELECT '1^^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3) AS cc
FROM temp

* 輸出結果

aa bb cc
1 3

* 使用regexp_substr (方法2 改良)

WITH temp AS
(
    SELECT '1^^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1, null, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2, null, 1) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3, null, 1) AS cc
FROM temp

* 輸出結果

aa bb cc
1 3

#oracle #SQL







Related Posts

Frequently Used Git Bash Commands

Frequently Used Git Bash Commands

Linkedin  Java 檢定題庫 static import

Linkedin Java 檢定題庫 static import

ASP.NET Core MVC 之語系切換

ASP.NET Core MVC 之語系切換


Comments